/*
 * Licensed to the Apache Software Foundation (ASF) under one
 * or more contributor license agreements.  See the NOTICE file
 * distributed with this work for additional information
 * regarding copyright ownership.  The ASF licenses this file
 * to you under the Apache License, Version 2.0 (the
 * "License"); you may not use this file except in compliance
 * with the License.  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing,
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied.  See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package org.apache.iotdb.db.it.withoutNull;

import org.apache.iotdb.it.env.ConfigFactory;
import org.apache.iotdb.it.env.EnvFactory;
import org.apache.iotdb.it.framework.IoTDBTestRunner;
import org.apache.iotdb.itbase.category.ClusterIT;
import org.apache.iotdb.itbase.category.LocalStandaloneIT;
import org.junit.*;
import org.junit.experimental.categories.Category;
import org.junit.runner.RunWith;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import static org.apache.iotdb.db.it.utils.TestUtils.resultSetEqualTest;
import static org.junit.Assert.fail;

@RunWith(IoTDBTestRunner.class)
@Category({LocalStandaloneIT.class, ClusterIT.class})
public class IoTDBWithoutNullAllFilterIT {

    private static String[] dataSet1 =
            new String[]{
                    "CREATE DATABASE root.test",
                    "CREATE TIMESERIES root.test.sg1.s1 WITH DATATYPE=BOOLEAN, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg1.s2 WITH DATATYPE=INT32, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg1.s3 WITH DATATYPE=DOUBLE, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg1.s4 WITH DATATYPE=INT32, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg2.s1 WITH DATATYPE=BOOLEAN, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg2.s2 WITH DATATYPE=INT32, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg2.s3 WITH DATATYPE=DOUBLE, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg2.s4 WITH DATATYPE=INT32, ENCODING=PLAIN",
                    "CREATE ALIGNED TIMESERIES root.test.sg3(s5 INT32, s6 BOOLEAN, s7 DOUBLE, s8 INT32)",
                    "CREATE TIMESERIES root.test.sg5.s1 WITH DATATYPE=BOOLEAN, ENCODING=PLAIN",
                    "CREATE TIMESERIES root.test.sg5.s9 WITH DATATYPE=INT32, ENCODING=PLAIN",
                    "INSERT INTO root.test.sg1(timestamp,s1,s2, s3, s4) " + "values(1, true, 1, 1.0, 1)",
                    "INSERT INTO root.test.sg2(timestamp,s1,s2, s3, s4) " + "values(1, false, 1, 1.0, 1)",
                    "INSERT INTO root.test.sg1(timestamp, s2) " + "values(2, 2)",
                    "INSERT INTO root.test.sg1(timestamp, s3) " + "values(2, 2.0)",
                    "INSERT INTO root.test.sg1(timestamp, s4) " + "values(2, 2)",
                    "INSERT INTO root.test.sg2(timestamp,s1,s2, s3, s4) " + "values(2, true, 2, 2.0, 2)",
                    "flush",
                    "INSERT INTO root.test.sg1(timestamp, s1) " + "values(3, false)",
                    "INSERT INTO root.test.sg1(timestamp, s2) " + "values(5, 5)",
                    "INSERT INTO root.test.sg1(timestamp, s3) " + "values(5, 5.0)",
                    "INSERT INTO root.test.sg1(timestamp, s4) " + "values(5, 5)",
                    "INSERT INTO root.test.sg2(timestamp, s2) " + "values(5, 5)",
                    "INSERT INTO root.test.sg2(timestamp, s3) " + "values(5, 5.0)",
                    "INSERT INTO root.test.sg2(timestamp, s4) " + "values(5, 5)",
                    "flush",
                    "INSERT INTO root.test.sg1(timestamp,s1,s2, s3, s4) " + "values(6, true, 6, 6.0, 6)",
                    "INSERT INTO root.test.sg2(timestamp,s1,s2, s3, s4) " + "values(6, true, 6, 6.0, 6)",
                    "INSERT INTO root.test.sg1(timestamp, s1) " + "values(7, true)",
                    "INSERT INTO root.test.sg1(timestamp, s3) " + "values(7, 7.0)",
                    "INSERT INTO root.test.sg2(timestamp,s1,s2, s3) " + "values(7, true, 7, 7.0)",
                    "flush",
                    "INSERT INTO root.test.sg1(timestamp, s1) " + "values(8, true)",
                    "INSERT INTO root.test.sg1(timestamp, s2) " + "values(8, 8)",
                    "INSERT INTO root.test.sg1(timestamp, s3) " + "values(8, 8.0)",
                    "INSERT INTO root.test.sg2(timestamp, s3) " + "values(8, 8.0)",
                    "INSERT INTO root.test.sg1(timestamp,s1,s2, s3, s4) " + "values(9, false, 9, 9.0, 9)",
                    "INSERT INTO root.test.sg2(timestamp, s1) " + "values(9, true)",
                    "flush",
                    "INSERT INTO root.test.sg2(timestamp, s2) " + "values(9, 9)",
                    "INSERT INTO root.test.sg2(timestamp, s4) " + "values(9, 9)",
                    "INSERT INTO root.test.sg1(timestamp,s1,s2, s3, s4) " + "values(10, true, 10, 10.0, 10)",
                    "INSERT INTO root.test.sg2(timestamp,s1,s2, s3, s4) " + "values(10, true, 10, 10.0, 10)",
                    "flush",
                    "INSERT INTO root.test.sg3(time, s5, s6, s7, s8) aligned values(1, 1, true, 1.0, 1)",
                    "INSERT INTO root.test.sg3(time, s6, s7, s8) aligned values(2, false, 2.0, 2)",
                    "INSERT INTO root.test.sg3(time, s5, s7, s8) aligned values(3, 3, 3.0, 3)",
                    "INSERT INTO root.test.sg3(time, s5) aligned values(5, 5)",
                    "INSERT INTO root.test.sg3(time, s5, s8) aligned values(6, 6, 6)",
                    "INSERT INTO root.test.sg3(time, s6) aligned values(7, true)",
                    "INSERT INTO root.test.sg3(time, s5, s7) aligned values(8, 8, 8.0)",
                    "INSERT INTO root.test.sg3(time, s5, s7, s8) aligned values(9, 9, 9.0, 9)",
                    "INSERT INTO root.test.sg3(time, s7, s8) aligned values(10, 10.0, 10)",
                    "INSERT INTO root.test.sg5(timestamp, s1, s9) " + "values(1, true, 1)",
                    "flush",
            };

    private static final String TIMESTAMP_STR = "Time";

    private static void prepareData() {
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            for (String sql : dataSet1) {
                statement.execute(sql);
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @BeforeClass
    public static void setUp() throws Exception {
        ConfigFactory.getConfig().setPartitionInterval(1000);
        EnvFactory.getEnv().initBeforeTest();
        prepareData();
    }

    @AfterClass
    public static void tearDown() throws Exception {
        ConfigFactory.getConfig().setPartitionInterval(86400);
        EnvFactory.getEnv().cleanAfterTest();
    }

    @Test
    public void rawDataWithoutValueFilterQueryTest() {
        System.out.println("rawDataWithoutValueFilterQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,true,1,1.0,1",
                        "2,null,2,2.0,2",
                        "5,null,5,5.0,5",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "10,true,10,10.0,10"
                };
        String[] retArray2 =
                new String[]{
                        "1,true,1,1.0,1",
                        "3,false,null,null,null",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "10,true,10,10.0,10"
                };
        String[] retArray3 =
                new String[]{
                        "1,true,1,1.0,1",
                        "2,null,2,2.0,2",
                        "3,false,null,null,null",
                        "5,null,5,5.0,5",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "10,true,10,10.0,10"
                };
        String[] retArray4 =
                new String[]{
                        "1,true,1,1.0,1",
                        "2,null,2,2.0,2",
                        "3,false,null,null,null",
                        "5,null,5,5.0,5",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "10,true,10,10.0,10"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select * from root.test.sg1 where s2 is not null || s3 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery("select * from root.test.sg1 where s1 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet = statement.executeQuery("select * from root.test.sg1")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select * from root.test.sg1 where s1 is not null || s2 is not null || s3 is not null || s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Test
    public void rawDataWithValueFilterQueryTest() {
        System.out.println("rawDataWithValueFilterQueryTest");
        String[] retArray1 = new String[]{"9,false,9,9.0,9"};
        String[] retArray2 = new String[]{"1,true,1,1.0,1"};
        String[] retArray3 =
                new String[]{
                        "1,true,1,1.0,1",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "10,true,10,10.0,10"
                };
        String[] retArray4 =
                new String[]{
                        "1,true,1,1.0,1",
                        "6,true,6,6.0,6",
                        "7,true,null,7.0,null",
                        "8,true,8,8.0,null",
                        "10,true,10,10.0,10"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select * from root.test.sg1 where s1 = false && (s2 is not null || s3 is not null)")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    // Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery("select * from root.test.sg1 where s2 = 1 && s1 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery("select * from root.test.sg1 where s2 = 2 && s1 is not null")) {
                Assert.assertFalse(resultSet.next());
            }

            try (ResultSet resultSet =
                         statement.executeQuery("select * from root.test.sg1 where s1 = true")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select * from root.test.sg1 where s1 = true && (s1 is not null || s2 is not null || s3 is not null || s4 is not null)")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4");
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Test
    public void withExpressionQueryTest() {
        System.out.println("withExpressionQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,1,-1,1,1,2.0,0.0,1.0,1.0,0.0",
                        "2,2,-2,2,2,4.0,0.0,4.0,1.0,0.0",
                        "5,5,-5,5,5,10.0,0.0,25.0,1.0,0.0",
                        "6,6,-6,6,6,12.0,0.0,36.0,1.0,0.0",
                        "9,9,-9,9,9,18.0,0.0,81.0,1.0,0.0",
                        "10,10,-10,10,10,20.0,0.0,100.0,1.0,0.0"
                };
        String[] retArray2 =
                new String[]{
                        "1,1,-1,1,1,2.0,0.0,1.0,1.0,0.0",
                        "2,2,-2,2,2,4.0,0.0,4.0,1.0,0.0",
                        "5,5,-5,5,5,10.0,0.0,25.0,1.0,0.0",
                        "6,6,-6,6,6,12.0,0.0,36.0,1.0,0.0",
                        "8,8,-8,null,null,null,null,null,null,null",
                        "9,9,-9,9,9,18.0,0.0,81.0,1.0,0.0",
                        "10,10,-10,10,10,20.0,0.0,100.0,1.0,0.0"
                };
        String[] retArray3 =
                new String[]{
                        "1,1,-1,1,1,2.0,0.0,1.0,1.0,0.0",
                        "2,2,-2,2,2,4.0,0.0,4.0,1.0,0.0",
                        "5,5,-5,5,5,10.0,0.0,25.0,1.0,0.0",
                        "6,6,-6,6,6,12.0,0.0,36.0,1.0,0.0",
                        "8,8,-8,null,null,null,null,null,null,null",
                        "9,9,-9,9,9,18.0,0.0,81.0,1.0,0.0",
                        "10,10,-10,10,10,20.0,0.0,100.0,1.0,0.0"
                };
        String[] retArray4 =
                new String[]{
                        "1,1,-1,1,1,2.0,0.0,1.0,1.0,0.0",
                        "2,2,-2,2,2,4.0,0.0,4.0,1.0,0.0",
                        "5,5,-5,5,5,10.0,0.0,25.0,1.0,0.0",
                        "6,6,-6,6,6,12.0,0.0,36.0,1.0,0.0",
                        "8,8,-8,null,null,null,null,null,null,null",
                        "9,9,-9,9,9,18.0,0.0,81.0,1.0,0.0",
                        "10,10,-10,10,10,20.0,0.0,100.0,1.0,0.0"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, - s2, s4, + s4, s2 + s4, s2 - s4, s2 * s4, s2 / s4, s2 % s4 from root.test.sg1 where s2 is not null && s4 is not null && s2 + s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("-root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 + root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 - root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 * root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 / root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 % root.test.sg1.s4");
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, - s2, s4, + s4, s2 + s4, s2 - s4, s2 * s4, s2 / s4, s2 % s4 from root.test.sg1 where s2 is not null || s4 is not null || s2 / s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("-root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 + root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 - root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 * root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 / root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 % root.test.sg1.s4");
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, - s2, s4, + s4, s2 + s4, s2 - s4, s2 * s4, s2 / s4, s2 % s4 from root.test.sg1"
                                         + " where s2 is not null || s4 is not null || s2 % s4 is not null || s2 % s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("-root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 + root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 - root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 * root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 / root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 % root.test.sg1.s4");
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, - s2, s4, + s4, s2 + s4, s2 - s4, s2 * s4, s2 / s4, s2 % s4 from root.test.sg1"
                                         + " where s2 is not null || s4 is not null || s2 / s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("-root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 + root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 - root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 * root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 / root.test.sg1.s4")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2 % root.test.sg1.s4");
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Test
    public void withUDFQueryTest() {
        System.out.println("withUDFQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,true,0.84,0.54,1.56",
                        "2,null,0.91,-0.42,-2.19",
                        "3,false,null,null,null",
                        "5,null,-0.96,0.28,-3.38",
                        "6,true,-0.28,0.96,-0.29",
                        "7,true,null,null,null",
                        "8,true,0.99,-0.15,-6.80",
                        "9,false,0.41,-0.91,-0.45",
                        "10,true,-0.54,-0.84,0.65"
                };
        String[] retArray2 =
                new String[]{
                        "1,true,1.38,-0.33",
                        "2,null,0.49,0.32",
                        "5,null,-0.68,-0.25",
                        "6,true,0.68,0.68",
                        "8,true,0.84,null",
                        "9,false,-0.50,-0.38",
                        "10,true,-1.38,-0.08"
                };
        String[] retArray3 =
                new String[]{
                        "1,true,-0.33",
                        "2,null,0.32",
                        "5,null,-0.25",
                        "6,true,0.68",
                        "9,false,-0.38",
                        "10,true,-0.08"
                };
        String[] retArray4 =
                new String[]{
                        "1,true,1.38,-0.33",
                        "2,null,0.49,0.32",
                        "3,false,null,null",
                        "5,null,-0.68,-0.25",
                        "6,true,0.68,0.68",
                        "7,true,null,null",
                        "8,true,0.84,null",
                        "9,false,-0.50,-0.38",
                        "10,true,-1.38,-0.08"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s1, sin(s2), cos(s2), tan(s2) from root.test.sg1 where sin(s2) is not null || s1 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + (resultSet.getString("sin(root.test.sg1.s2)") == null
                                    || resultSet.getString("sin(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("sin(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("cos(root.test.sg1.s2)") == null
                                    || resultSet.getString("cos(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("cos(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("tan(root.test.sg1.s2)") == null
                                    || resultSet.getString("tan(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("tan(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            String[] columns =
                    new String[]{
                            "root.test.sg1.s1",
                            "sin(root.test.sg1.s2) + cos(root.test.sg1.s2)",
                            "cos(sin(root.test.sg1.s2 + root.test.sg1.s4) + root.test.sg1.s2)"
                    };

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s1, sin(s2) + cos(s2), cos(sin(s2 + s4) + s2) from root.test.sg1 where sin(s2) + cos(s2) is not null || cos(sin(s2 + s4) + s2) is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + (resultSet.getString(columns[1]) == null
                                    || resultSet.getString(columns[1]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[1]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[2]) == null
                                    || resultSet.getString(columns[2]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[2]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s1, cos(sin(s2 + s4) + s2) from root.test.sg1 where (cos(sin(s2 + s4) + s2)) is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + (resultSet.getString(columns[2]) == null
                                    || resultSet.getString(columns[2]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[2]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s1, sin(s2) + cos(s2), cos(sin(s2 + s4) + s2) from root.test.sg1 where  s1 is not null || s2 is not null || s4 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + (resultSet.getString(columns[1]) == null
                                    || resultSet.getString(columns[1]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[1]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[2]) == null
                                    || resultSet.getString(columns[2]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[2]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Ignore
    @Test
    public void withGroupByTimeQueryTest() {
        System.out.println("withGroupByTimeQueryTest");
        String[] retArray1 = new String[]{"1,1.50,3.00", "5,5.50,11.00", "7,null,8.00", "9,9.00,9.00"};
        String[] retArray2 =
                new String[]{"1,1.50,3.00,2", "5,5.50,11.00,2", "7,null,8.00,2", "9,9.00,9.00,1"};
        String[] retArray3 =
                new String[]{
                        "1,1.00,1.00", "2,2.00,2.00", "5,5.00,5.00", "6,6.00,6.00", "8,null,8.00", "9,9.00,9.00"
                };
        String[] retArray4 =
                new String[]{
                        "1,1.50,3.00,1.50", "5,5.50,11.00,5.50", "7,null,15.00,8.00", "9,9.00,9.00,9.00"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s4), sum(s2) from root.test.sg1 group by ([1,10), 2ms) without null all(sum(s2))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString("avg(root.test.sg1.s4)") == null
                                    || resultSet.getString("avg(root.test.sg1.s4)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("avg(root.test.sg1.s4)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("sum(root.test.sg1.s2)") == null
                                    || resultSet.getString("sum(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("sum(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            String[] columns =
                    new String[]{
                            "avg(root.test.sg1.s4)", "sum(root.test.sg1.s2)", "count(root.test.sg1.s3)"
                    };

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s4), sum(s2), count(s3) from root.test.sg1 group by ([1,10), 2ms) without null all(avg(s4), sum(s2))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString(columns[0]) == null
                                    || resultSet.getString(columns[0]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[0]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[1]) == null
                                    || resultSet.getString(columns[1]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[1]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + resultSet.getString(columns[2]);
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s4) as t, avg(s2) from root.test.sg1 group by ([1,10), 1ms) without null all(t, avg(s2))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString("t") == null || resultSet.getString("t").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("t"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("avg(root.test.sg1.s2)") == null
                                    || resultSet.getString("avg(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("avg(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s4), sum(s3) as t, avg(s2) from root.test.sg1 group by ([1,10), 2ms) without null all(t, avg(s2))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString("avg(root.test.sg1.s4)") == null
                                    || resultSet.getString("avg(root.test.sg1.s4)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("avg(root.test.sg1.s4)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("t") == null || resultSet.getString("t").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("t"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("avg(root.test.sg1.s2)") == null
                                    || resultSet.getString("avg(root.test.sg1.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("avg(root.test.sg1.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Ignore
    @Test
    public void withGroupByLevelQueryTest() {
        System.out.println("withGroupByLevelQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,1.50,1.50,3.00,3.00",
                        "5,5.50,5.50,11.00,11.00",
                        "7,8.00,7.00,null,null",
                        "9,9.00,9.00,9.00,9.00"
                };
        String[] retArray2 =
                new String[]{
                        "1,1.50,1.50,3.00,3.00",
                        "5,5.50,5.50,11.00,11.00",
                        "7,8.00,7.00,null,null",
                        "9,9.00,9.00,9.00,9.00"
                };
        String[] retArray3 = new String[]{"1,1.50,3.00", "5,5.50,11.00", "7,8.00,null", "9,9.00,9.00"};
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            String[] columns =
                    new String[]{
                            "avg(root.*.sg1.s2)", "avg(root.*.sg2.s2)", "sum(root.*.sg1.s4)", "sum(root.*.sg2.s4)"
                    };

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s2), sum(s4) from root.test.** group by ([1, 10), 2ms), level = 2 without null all(avg(s2))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString(columns[0]) == null
                                    || resultSet.getString(columns[0]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[0]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[1]) == null
                                    || resultSet.getString(columns[1]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[1]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[2]) == null
                                    || resultSet.getString(columns[2]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[2]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[3]) == null
                                    || resultSet.getString(columns[3]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[3]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s2), sum(s4) from root.test.** group by ([1, 10), 2ms), level = 2 without null all(avg(s2), sum(s4))")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString(columns[0]) == null
                                    || resultSet.getString(columns[0]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[0]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[1]) == null
                                    || resultSet.getString(columns[1]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[1]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[2]) == null
                                    || resultSet.getString(columns[2]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[2]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString(columns[3]) == null
                                    || resultSet.getString(columns[3]).equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString(columns[3]))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select avg(s2), sum(s4) as t from root.*.sg1 group by ([1, 10), 2ms), level = 1 without null all(avg(s2), t)")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + (resultSet.getString("avg(root.test.*.s2)") == null
                                    || resultSet.getString("avg(root.test.*.s2)").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("avg(root.test.*.s2)"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString())
                                    + ","
                                    + (resultSet.getString("t") == null || resultSet.getString("t").equals("null")
                                    ? "null"
                                    : new BigDecimal(resultSet.getString("t"))
                                    .setScale(2, RoundingMode.HALF_UP)
                                    .toPlainString());
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Ignore
    @Test
    public void withoutNullColumnsIsFullPathQueryTest() {
        // select s2, s3 from root.test.** without null all(root.test.sg1.s2, root.test.sg2.s3)
        // select s2, s3 from root.test.sg1, root.test.sg2 without null all(root.test.sg1.s2)
        // select s2, s3 from root.test.sg1, root.test.sg2 without null all(root.test.sg1.s2, s3)
        System.out.println("withoutNullColumnsIsFullPathQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,1,1.0,1,1.0",
                        "2,2,2.0,2,2.0",
                        "5,5,5.0,5,5.0",
                        "6,6,6.0,6,6.0",
                        "7,null,7.0,7,7.0",
                        "8,8,8.0,null,8.0",
                        "9,9,9.0,9,null",
                        "10,10,10.0,10,10.0"
                };
        String[] retArray2 =
                new String[]{
                        "1,1,1.0,1,1.0",
                        "2,2,2.0,2,2.0",
                        "5,5,5.0,5,5.0",
                        "6,6,6.0,6,6.0",
                        "8,8,8.0,null,8.0",
                        "9,9,9.0,9,null",
                        "10,10,10.0,10,10.0"
                };
        String[] retArray3 =
                new String[]{
                        "1,1,1.0,1,1.0",
                        "2,2,2.0,2,2.0",
                        "5,5,5.0,5,5.0",
                        "6,6,6.0,6,6.0",
                        "7,null,7.0,7,7.0",
                        "8,8,8.0,null,8.0",
                        "9,9,9.0,9,null",
                        "10,10,10.0,10,10.0"
                };

        String[] retArray4 =
                new String[]{
                        "1,1,1.0,1,1.0",
                        "2,2,2.0,2,2.0",
                        "5,5,5.0,5,5.0",
                        "6,6,6.0,6,6.0",
                        "7,null,7.0,7,7.0",
                        "8,8,8.0,null,8.0",
                        "9,9,9.0,9,null",
                        "10,10,10.0,10,10.0"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            String[] columns =
                    new String[]{
                            "root.test.sg1.s2", "root.test.sg1.s3", "root.test.sg2.s2", "root.test.sg2.s3"
                    };

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, s3 from root.test.** where root.test.sg1.s2 is not null || root.test.sg2.s3 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, s3 from root.test.sg1, root.test.sg2 where root.test.sg1.s2 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, s3 from root.test.sg1, root.test.sg2 where root.test.sg1.s2 is not null || s3 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s2, s3 from root.test.sg1, root.test.sg2 where root.test.*.s2 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    // meta check whether the specified exception occur
    @Test
    public void withoutNullColumnsMisMatchSelectedQueryTest() {
        System.out.println("withoutNullColumnsMisMatchSelectedQueryTest");
        String[] expectedHeader =
                new String[]{
                        TIMESTAMP_STR,
                        "root.test.sg1.s1",
                        "root.test.sg1.s2",
                        "root.test.sg1.s3",
                        "root.test.sg1.s4",
                };
        String[] retArray =
                new String[]{
                        "1,true,1,1.0,1,",
                        "3,false,null,null,null,",
                        "6,true,6,6.0,6,",
                        "7,true,null,7.0,null,",
                        "8,true,8,8.0,null,",
                        "9,false,9,9.0,9,",
                        "10,true,10,10.0,10,"
                };
        resultSetEqualTest(
                "select s1, s2, s3, s4 from root.test.sg1 where s1 is not null || usage is not null",
                expectedHeader,
                retArray);
    }

    @Ignore
    @Test
    public void alignByDeviceQueryTest() {
        System.out.println("alignByDeviceQueryTest");
        String[] retArray1 =
                new String[]{"1,true,2,2.0,2", "5,true,6,6.0,6", "7,true,8,8.0,null", "9,false,9,9.0,9"};
        String[] retArray2 =
                new String[]{"1,true,2,2.0,2", "5,true,6,6.0,6", "7,true,8,8.0,null", "9,false,9,9.0,9"};
        String[] retArray3 =
                new String[]{
                        "1,true,2,2.0,2",
                        "5,true,6,6.0,6",
                        "7,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "1,true,2,2.0,2",
                        "5,true,6,6.0,6",
                        "7,true,7,8.0,null",
                        "9,true,9,null,9"
                };
        String[] retArray4 =
                new String[]{
                        "1,true,2,2.0,2",
                        "5,true,6,6.0,6",
                        "7,true,8,8.0,null",
                        "9,false,9,9.0,9",
                        "1,true,2,2.0,2",
                        "5,true,6,6.0,6",
                        "7,true,7,8.0,null",
                        "9,true,9,null,9"
                };

        String[] retArray5 = new String[]{"1,2,2.0,2", "5,6,6.0,6", "7,8,8.0,null", "9,9,9.0,9"};
        String[] retArray6 =
                new String[]{
                        "true,1,null",
                        "null,2,null",
                        "null,5,null",
                        "true,6,null",
                        "true,8,null",
                        "false,9,null",
                        "true,10,null"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            String[] columns =
                    new String[]{"last_value(s1)", "last_value(s2)", "last_value(s3)", "last_value(s4)"};

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.sg1 group by([1,10), 2ms) without null all(last_value(s2), last_value(s3)) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.sg1 group by([1,10), 2ms) without null all(last_value(s2)) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.* group by([1,10), 2ms) without null all(last_value(s2)) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.* group by([1,10), 2ms) without null all(last_value(s2), last_value(s3)) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(s2) as t, last_value(s3), last_value(s4) from root.test.sg1 group by([1,10), 2ms) without null all(t, last_value(s3)) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("t")
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray5[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray5.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select s1,s2,s9 from root.test.sg1,root.test.sg5 without null all(s2) align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString("s1")
                                    + ","
                                    + resultSet.getString("s2")
                                    + ","
                                    + resultSet.getString("s9");
                    Assert.assertEquals(retArray6[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray6.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Test
    public void alignTimeSeriesQueryTest() {
        System.out.println("alignTimeSeriesQueryTest");
        String[] retArray1 =
                new String[]{
                        "1,true,1,1,true,1.0,1,1.0",
                        "2,null,2,null,false,2.0,2,2.0",
                        "5,null,5,5,null,null,null,5.0",
                        "6,true,6,6,null,null,6,6.0",
                        "8,true,8,8,null,8.0,null,8.0",
                        "9,false,9,9,null,9.0,9,null",
                        "10,true,10,null,null,10.0,10,10.0"
                };
        String[] retArray2 = new String[]{"1,true,1,1.0,1,true,1.0,1"};
        String[] retArray3 =
                new String[]{
                        "1,true,1,1.0,1,true,1.0,1",
                        "2,null,2,2.0,null,false,2.0,2",
                        "3,false,null,null,3,null,3.0,3",
                        "5,null,5,5.0,5,null,null,null",
                        "6,true,6,6.0,6,null,null,6",
                        "7,true,null,7.0,null,true,null,null",
                        "8,true,8,8.0,8,null,8.0,null",
                        "9,false,9,null,9,null,9.0,9"
                };
        String[] retArray4 =
                new String[]{
                        "1,true,1,1.0,1,true,1.0,1",
                        "2,null,2,2.0,null,false,2.0,2",
                        "3,false,null,null,3,null,3.0,3",
                        "5,null,5,5.0,5,null,null,null",
                        "6,true,6,6.0,6,null,null,6",
                        "7,true,null,7.0,null,true,null,null",
                        "8,true,8,8.0,8,null,8.0,null",
                        "9,false,9,null,9,null,9.0,9",
                        "10,true,10,10.0,null,null,10.0,10"
                };
        String[] retArray5 =
                new String[]{
                        "1,true,1,1.0,1,true,1.0,1",
                        "2,null,2,2.0,null,false,2.0,2",
                        "3,false,null,null,3,null,3.0,3",
                        "5,null,5,5.0,5,null,null,null",
                        "6,true,6,6.0,6,null,null,6",
                        "7,true,null,7.0,null,true,null,null",
                        "8,true,8,8.0,8,null,8.0,null",
                        "9,false,9,null,9,null,9.0,9",
                        "10,true,10,10.0,null,null,10.0,10"
                };
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select sg1.s1, sg1.s2, sg3.*, sg2.s3 from root.test where sg3.* is not null || sg1.s2 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s5")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s6")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s7")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s8")
                                    + ","
                                    + resultSet.getString("root.test.sg2.s3");
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select sg1.s1, sg1.s2, sg2.s3, sg3.* from root.test where sg3.* is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg2.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s5")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s6")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s7")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s8");
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select sg1.s1, sg1.s2, sg2.s3, sg3.* from root.test where sg3.s5 is not null || sg3.s6 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg2.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s5")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s6")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s7")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s8");
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select sg1.s1, sg1.s2, sg2.s3, sg3.* from root.test where sg3.s5 is not null || sg3.s6 is not null || sg2.s3 is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg2.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s5")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s6")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s7")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s8");
                    Assert.assertEquals(retArray4[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray4.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select sg1.s1, sg1.s2, sg2.s3, sg3.* from root.test where sg1.s1 is not null || sg1.s2 is not null || sg2.s3 is not null || sg3.* is not null")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString("root.test.sg1.s1")
                                    + ","
                                    + resultSet.getString("root.test.sg1.s2")
                                    + ","
                                    + resultSet.getString("root.test.sg2.s3")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s5")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s6")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s7")
                                    + ","
                                    + resultSet.getString("root.test.sg3.s8");
                    Assert.assertEquals(retArray5[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray5.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }

    @Ignore
    @Test
    public void withLimitOffsetQueryTest() {
        // last_value(s3) | last_value(s4) | last_value(s1) | last_value(s2)
        System.out.println("withLimitOffsetQueryTest");
        String[] retArray1 = new String[]{"9,false,9,9.0,9"};
        String[] retArray2 = new String[]{"7,true,8.0,null", "9,false,9.0,9"};
        String[] retArray3 = new String[]{"7,true,8,null", "9,false,9,9"};
        String[] columns =
                new String[]{"last_value(s1)", "last_value(s2)", "last_value(s3)", "last_value(s4)"};
        try (Connection connection = EnvFactory.getEnv().getConnection();
             Statement statement = connection.createStatement()) {

            int cnt;
            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.sg1 group by([1,10), 2ms) without null all(last_value(s2), last_value(s3)) limit 5 offset 3 align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray1[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray1.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.sg1 group by([1,10), 2ms) without null all(last_value(s4), last_value(s3)) limit 5 offset 2 slimit 3 align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[2])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray2[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray2.length, cnt);
            }

            try (ResultSet resultSet =
                         statement.executeQuery(
                                 "select last_value(*) from root.test.sg1 group by([1,10), 2ms) without null all(last_value(s2)) limit 5 offset 2 slimit 3 soffset 1 align by device")) {
                cnt = 0;
                while (resultSet.next()) {
                    String ans =
                            resultSet.getString(TIMESTAMP_STR)
                                    + ","
                                    + resultSet.getString(columns[0])
                                    + ","
                                    + resultSet.getString(columns[1])
                                    + ","
                                    + resultSet.getString(columns[3]);
                    Assert.assertEquals(retArray3[cnt], ans);
                    cnt++;
                }
                Assert.assertEquals(retArray3.length, cnt);
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        }
    }
}
